package com.ks.sgms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ks.sgms.entity.Score;
import com.ks.sgms.entity.Student;
import com.ks.sgms.util.DbHelper;

public class ScoreDao {
	String sqlHead = "select student.student_id,student_name,course_name,score,term,score.course_id,class_name"
			+ " from score,course,student,class";
	
	
	public List<Score> getAllScore() { 
		List<Score> list = new ArrayList<Score>(); 
		Connection conn = DbHelper.getConnection();
		String sql = sqlHead
				+ " where score.course_id = course.course_id"
				+ " and score.student_id = student.student_id"
				+ " and student.class_id = class.class_id"; 
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Score score = new Score();
				score.student_id = rst.getString("student_id");
				score.student_name = rst.getString("student_name");
				score.course_name = rst.getString("course_name");
				score.course_id = rst.getString("course_id");
				score.class_name = rst.getString("class_name");
				score.score = Integer.parseInt(rst.getString("score"));
				score.term = Integer.parseInt(rst.getString("term"));
				list.add(score);
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace(); 
		}
		return list;
	}
	
	public List<Score> selectScoreByStudentId(String stuNo) {
		Connection conn = DbHelper.getConnection();
		List<Score> list = new ArrayList<Score>();
		String sql = sqlHead
				+ " where score.course_id = course.course_id"
				+ " and score.student_id = student.student_id "
				+ "and student.class_id = class.class_id"
				+ " and score.student_id = ?"; 
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, stuNo);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Score score = new Score();
				score.student_id = rst.getString("student_id");
				score.student_name = rst.getString("student_name");
				score.course_name = rst.getString("course_name");
				score.course_id = rst.getString("course_id");
				score.class_name = rst.getString("class_name");
				score.score = Integer.parseInt(rst.getString("score"));
				score.term = Integer.parseInt(rst.getString("term"));
				list.add(score);
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List<Score> selectScoreByClass(String class_name) {
		Connection conn = DbHelper.getConnection();
		String sql = sqlHead
				+ " where score.course_id = course.course_id"
				+ " and score.student_id = student.student_id "
				+ "and student.class_id = class.class_id"
				+ " and student.class_id = ("
				+ "select class_id from class where class_name = ?"
				+ ")"; 
		List<Score> list = new ArrayList<Score>();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, class_name);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Score score = new Score();
				score.student_id = rst.getString("student_id");
				score.student_name = rst.getString("student_name");
				score.course_name = rst.getString("course_name");
				score.course_id = rst.getString("course_id");
				score.class_name = rst.getString("class_name");
				score.score = Integer.parseInt(rst.getString("score"));
				score.term = Integer.parseInt(rst.getString("term"));
				list.add(score);
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public boolean addScore(Score s) {
		String sql = "INSERT INTO score(student_id,course_id,score,term) VALUES (?,?,?,?)"; 
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, s.student_id);
			pst.setString(2, s.course_id);
			pst.setString(3, s.score+"");
			pst.setString(4, s.term+"");
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	public Score selectScoreByStudentAndCourse(String student_id,String course_id) {
		Connection conn = DbHelper.getConnection();
		Score score = new Score();
		String sql = sqlHead
				+ " where score.course_id = course.course_id"
				+ " and score.student_id = student.student_id "
				+ "and student.class_id = class.class_id"
				+ " and score.student_id = ?"
				+ "	and score.course_id = ?"; 
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, student_id);
			pst.setString(2, course_id);
			ResultSet rst = pst.executeQuery();
			if (rst.next()) {
				score.student_id = rst.getString("student_id");
				score.student_name = rst.getString("student_name");
				score.course_name = rst.getString("course_name");
				score.course_id = rst.getString("course_id");
				score.class_name = rst.getString("class_name");
				score.score = Integer.parseInt(rst.getString("score"));
				score.term = Integer.parseInt(rst.getString("term"));
			}
			pst.close();
			rst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return score;
	}
	
	public boolean deleteScoreByStudentAndCourse(String student_id,String course_id) {
		String sql = "DELETE FROM score WHERE student_id = ? and course_id = ?";
		Connection conn = DbHelper.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, student_id);
			pst.setString(2, course_id);
			int count = pst.executeUpdate();
			pst.close();
			System.out.println("删除"+count+"个成绩");
			return count > 0 ? true : false; 
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean updateScore(Score score) {
		String sql = "UPDATE score SET student_id=?,course_id=?,score=?,term=? where student_id = ? and course_id = ?";
		Connection connection = DbHelper.getConnection();
		try {
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, score.student_id);
			pst.setString(2, score.course_id);
			pst.setString(3, score.score+"");
			pst.setString(4, score.term+"");
			pst.setString(5, score.student_id);
			pst.setString(6, score.course_id);
			int count = pst.executeUpdate();
			pst.close();
			return count > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}
